package com.pethospital.dao.impl;

import com.pethospital.dao.PetDao;
import com.pethospital.entity.PageResult;
import com.pethospital.entity.Pet;
import com.pethospital.entity.User;
import com.pethospital.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 宠物数据访问实现类
 * Pet Data Access Object Implementation
 * 
 * @author PetHospital System
 * @version 1.0
 */
public class PetDaoImpl implements PetDao {
    
    @Override
    public Pet findById(Integer petId) {
        String sql = "SELECT p.*, u.real_name as owner_name FROM pets p " +
                    "LEFT JOIN users u ON p.owner_id = u.user_id WHERE p.pet_id = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        
        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, petId);
            rs = pstmt.executeQuery();
            
            if (rs.next()) {
                return mapResultSetToPet(rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }
        
        return null;
    }
    
    @Override
    public List<Pet> findByOwnerId(Integer ownerId) {
        String sql = "SELECT p.*, u.real_name as owner_name FROM pets p " +
                    "LEFT JOIN users u ON p.owner_id = u.user_id WHERE p.owner_id = ? ORDER BY p.create_time DESC";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Pet> pets = new ArrayList<>();

        System.out.println("DEBUG: PetDaoImpl.findByOwnerId() 执行SQL查询，ownerId: " + ownerId);
        System.out.println("DEBUG: SQL: " + sql);

        try {
            conn = DBUtil.getConnection();
            System.out.println("DEBUG: 数据库连接获取成功: " + (conn != null));

            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, ownerId);
            rs = pstmt.executeQuery();

            int count = 0;
            while (rs.next()) {
                Pet pet = mapResultSetToPet(rs);
                pets.add(pet);
                count++;
                System.out.println("DEBUG: 找到宠物 " + count + " - ID: " + pet.getPetId() +
                                 ", 名称: " + pet.getPetName() +
                                 ", 类型: " + pet.getPetType() +
                                 ", 品种: " + pet.getBreed() +
                                 ", 主人ID: " + pet.getOwnerId());
            }

            System.out.println("DEBUG: 总共找到 " + count + " 只宠物");

        } catch (SQLException e) {
            System.out.println("DEBUG: 数据库查询异常: " + e.getMessage());
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }

        return pets;
    }

    @Override
    public List<Pet> findRecentByOwnerId(Integer ownerId, int limit) {
        String sql = "SELECT p.*, u.real_name as owner_name FROM pets p " +
                    "LEFT JOIN users u ON p.owner_id = u.user_id " +
                    "WHERE p.owner_id = ? " +
                    "ORDER BY p.create_time DESC LIMIT ?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Pet> pets = new ArrayList<>();

        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, ownerId);
            pstmt.setInt(2, limit);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                pets.add(mapResultSetToPet(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }

        return pets;
    }

    @Override
    public int insert(Pet pet) {
        String sql = "INSERT INTO pets (pet_name, pet_type, breed, gender, age, weight, color, " +
                    "owner_id, description, photo_url, status) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        Connection conn = null;
        PreparedStatement pstmt = null;
        
        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, pet.getPetName());
            pstmt.setString(2, pet.getPetType());
            pstmt.setString(3, pet.getBreed());
            pstmt.setString(4, pet.getGender());
            pstmt.setObject(5, pet.getAge());
            pstmt.setBigDecimal(6, pet.getWeight());
            pstmt.setString(7, pet.getColor());
            pstmt.setInt(8, pet.getOwnerId());
            pstmt.setString(9, pet.getDescription());
            pstmt.setString(10, pet.getPhotoUrl());
            pstmt.setString(11, pet.getStatus() != null ? pet.getStatus() : "healthy");
            
            return pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, null);
        }
        
        return 0;
    }
    
    @Override
    public int update(Pet pet) {
        String sql = "UPDATE pets SET pet_name = ?, pet_type = ?, breed = ?, gender = ?, " +
                    "age = ?, weight = ?, color = ?, description = ?, photo_url = ?, status = ? WHERE pet_id = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, pet.getPetName());
            pstmt.setString(2, pet.getPetType());
            pstmt.setString(3, pet.getBreed());
            pstmt.setString(4, pet.getGender());
            pstmt.setObject(5, pet.getAge());
            pstmt.setBigDecimal(6, pet.getWeight());
            pstmt.setString(7, pet.getColor());
            pstmt.setString(8, pet.getDescription());
            pstmt.setString(9, pet.getPhotoUrl());
            pstmt.setString(10, pet.getStatus());
            pstmt.setInt(11, pet.getPetId());

            return pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, null);
        }

        return 0;
    }
    
    @Override
    public int delete(Integer petId) {
        String sql = "DELETE FROM pets WHERE pet_id = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        
        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, petId);
            
            return pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, null);
        }
        
        return 0;
    }
    
    @Override
    public int batchDelete(Integer[] petIds) {
        if (petIds == null || petIds.length == 0) {
            return 0;
        }
        
        StringBuilder sql = new StringBuilder("DELETE FROM pets WHERE pet_id IN (");
        for (int i = 0; i < petIds.length; i++) {
            sql.append("?");
            if (i < petIds.length - 1) {
                sql.append(",");
            }
        }
        sql.append(")");
        
        Connection conn = null;
        PreparedStatement pstmt = null;
        
        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql.toString());
            for (int i = 0; i < petIds.length; i++) {
                pstmt.setInt(i + 1, petIds[i]);
            }
            
            return pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, null);
        }
        
        return 0;
    }
    
    @Override
    public List<Pet> findAll() {
        String sql = "SELECT p.*, u.real_name as owner_name FROM pets p " +
                    "LEFT JOIN users u ON p.owner_id = u.user_id ORDER BY p.create_time DESC";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Pet> pets = new ArrayList<>();
        
        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            
            while (rs.next()) {
                pets.add(mapResultSetToPet(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }
        
        return pets;
    }
    
    @Override
    public PageResult<Pet> findByPage(int currentPage, int pageSize) {
        return findByCondition(null, null, null, null, null, currentPage, pageSize);
    }
    
    @Override
    public PageResult<Pet> findByOwnerIdAndPage(Integer ownerId, int currentPage, int pageSize) {
        return findByCondition(null, null, null, null, ownerId, currentPage, pageSize);
    }
    
    @Override
    public PageResult<Pet> findByCondition(String petName, String petType, String breed, 
                                         String status, Integer ownerId, int currentPage, int pageSize) {
        StringBuilder sql = new StringBuilder("SELECT p.*, u.real_name as owner_name FROM pets p " +
                                            "LEFT JOIN users u ON p.owner_id = u.user_id WHERE 1=1");
        List<Object> params = new ArrayList<>();
        
        // 构建查询条件
        if (petName != null && !petName.trim().isEmpty()) {
            sql.append(" AND p.pet_name LIKE ?");
            params.add("%" + petName.trim() + "%");
        }
        if (petType != null && !petType.trim().isEmpty()) {
            sql.append(" AND p.pet_type = ?");
            params.add(petType);
        }
        if (breed != null && !breed.trim().isEmpty()) {
            sql.append(" AND p.breed LIKE ?");
            params.add("%" + breed.trim() + "%");
        }
        if (status != null && !status.trim().isEmpty()) {
            sql.append(" AND p.status = ?");
            params.add(status);
        }
        if (ownerId != null) {
            sql.append(" AND p.owner_id = ?");
            params.add(ownerId);
        }
        
        // 先查询总数
        long totalCount = countByCondition(petName, petType, breed, status, ownerId);
        
        // 添加排序和分页
        sql.append(" ORDER BY p.create_time DESC LIMIT ?, ?");
        params.add((currentPage - 1) * pageSize);
        params.add(pageSize);
        
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Pet> pets = new ArrayList<>();
        
        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql.toString());
            
            // 设置参数
            for (int i = 0; i < params.size(); i++) {
                pstmt.setObject(i + 1, params.get(i));
            }
            
            rs = pstmt.executeQuery();
            while (rs.next()) {
                pets.add(mapResultSetToPet(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }
        
        return new PageResult<>(pets, currentPage, pageSize, totalCount);
    }

    @Override
    public PageResult<Pet> findByConditionWithSort(String petName, String petType, String breed,
                                                  String status, Integer ownerId, String sortField,
                                                  String sortOrder, int currentPage, int pageSize) {
        StringBuilder sql = new StringBuilder("SELECT p.*, u.real_name as owner_name FROM pets p " +
                                            "LEFT JOIN users u ON p.owner_id = u.user_id WHERE 1=1");
        List<Object> params = new ArrayList<>();

        // 构建查询条件
        if (petName != null && !petName.trim().isEmpty()) {
            sql.append(" AND p.pet_name LIKE ?");
            params.add("%" + petName.trim() + "%");
        }

        if (petType != null && !petType.trim().isEmpty()) {
            sql.append(" AND p.pet_type = ?");
            params.add(petType.trim());
        }

        if (breed != null && !breed.trim().isEmpty()) {
            sql.append(" AND p.breed LIKE ?");
            params.add("%" + breed.trim() + "%");
        }

        if (status != null && !status.trim().isEmpty()) {
            sql.append(" AND p.status = ?");
            params.add(status.trim());
        }

        if (ownerId != null) {
            sql.append(" AND p.owner_id = ?");
            params.add(ownerId);
        }

        // 添加排序
        if (sortField != null && !sortField.trim().isEmpty()) {
            String field = sortField.trim();
            // 安全检查，只允许特定字段排序
            if ("createTime".equals(field) || "petName".equals(field) || "petType".equals(field)) {
                sql.append(" ORDER BY ");
                if ("createTime".equals(field)) {
                    sql.append("p.create_time");
                } else if ("petName".equals(field)) {
                    sql.append("p.pet_name");
                } else if ("petType".equals(field)) {
                    sql.append("p.pet_type");
                }

                if ("desc".equalsIgnoreCase(sortOrder)) {
                    sql.append(" DESC");
                } else {
                    sql.append(" ASC");
                }
            }
        }

        // 添加分页
        sql.append(" LIMIT ?, ?");

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Pet> pets = new ArrayList<>();

        // 先获取总数
        long totalCount = countByConditionWithSort(petName, petType, breed, status, ownerId);

        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql.toString());

            for (int i = 0; i < params.size(); i++) {
                pstmt.setObject(i + 1, params.get(i));
            }
            pstmt.setInt(params.size() + 1, (currentPage - 1) * pageSize);
            pstmt.setInt(params.size() + 2, pageSize);

            rs = pstmt.executeQuery();
            while (rs.next()) {
                pets.add(mapResultSetToPet(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }

        return new PageResult<>(pets, currentPage, pageSize, totalCount);
    }

    /**
     * 根据条件统计宠物数量（用于排序查询）
     */
    private long countByConditionWithSort(String petName, String petType, String breed, String status, Integer ownerId) {
        StringBuilder sql = new StringBuilder("SELECT COUNT(*) FROM pets WHERE 1=1");
        List<Object> params = new ArrayList<>();

        if (petName != null && !petName.trim().isEmpty()) {
            sql.append(" AND pet_name LIKE ?");
            params.add("%" + petName.trim() + "%");
        }

        if (petType != null && !petType.trim().isEmpty()) {
            sql.append(" AND pet_type = ?");
            params.add(petType.trim());
        }

        if (breed != null && !breed.trim().isEmpty()) {
            sql.append(" AND breed LIKE ?");
            params.add("%" + breed.trim() + "%");
        }

        if (status != null && !status.trim().isEmpty()) {
            sql.append(" AND status = ?");
            params.add(status.trim());
        }

        if (ownerId != null) {
            sql.append(" AND owner_id = ?");
            params.add(ownerId);
        }

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql.toString());

            for (int i = 0; i < params.size(); i++) {
                pstmt.setObject(i + 1, params.get(i));
            }

            rs = pstmt.executeQuery();
            if (rs.next()) {
                return rs.getLong(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }

        return 0;
    }

    /**
     * 将ResultSet映射为Pet对象
     * 
     * @param rs ResultSet对象
     * @return Pet 宠物对象
     * @throws SQLException SQL异常
     */
    private Pet mapResultSetToPet(ResultSet rs) throws SQLException {
        Pet pet = new Pet();
        pet.setPetId(rs.getInt("pet_id"));
        pet.setPetName(rs.getString("pet_name"));
        pet.setPetType(rs.getString("pet_type"));
        pet.setBreed(rs.getString("breed"));
        pet.setGender(rs.getString("gender"));
        pet.setAge(rs.getObject("age", Integer.class));
        pet.setWeight(rs.getBigDecimal("weight"));
        pet.setColor(rs.getString("color"));
        pet.setOwnerId(rs.getInt("owner_id"));
        pet.setDescription(rs.getString("description"));
        pet.setPhotoUrl(rs.getString("photo_url"));
        pet.setStatus(rs.getString("status"));
        pet.setCreateTime(rs.getTimestamp("create_time"));
        pet.setUpdateTime(rs.getTimestamp("update_time"));
        
        // 设置主人信息
        String ownerName = rs.getString("owner_name");
        if (ownerName != null) {
            User owner = new User();
            owner.setUserId(rs.getInt("owner_id"));
            owner.setRealName(ownerName);
            pet.setOwner(owner);
        }
        
        return pet;
    }

    @Override
    public long countAll() {
        String sql = "SELECT COUNT(*) FROM pets";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                return rs.getLong(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }

        return 0;
    }

    @Override
    public long countByOwnerId(Integer ownerId) {
        String sql = "SELECT COUNT(*) FROM pets WHERE owner_id = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, ownerId);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                return rs.getLong(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }

        return 0;
    }

    @Override
    public long countByPetType(String petType) {
        String sql = "SELECT COUNT(*) FROM pets WHERE pet_type = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, petType);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                return rs.getLong(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }

        return 0;
    }

    @Override
    public long countByStatus(String status) {
        String sql = "SELECT COUNT(*) FROM pets WHERE status = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, status);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                return rs.getLong(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }

        return 0;
    }

    @Override
    public long countTodayRegistered() {
        String sql = "SELECT COUNT(*) FROM pets WHERE DATE(create_time) = CURDATE()";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                return rs.getLong(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }

        return 0;
    }

    @Override
    public long countTodayRegisteredByOwnerId(Integer ownerId) {
        String sql = "SELECT COUNT(*) FROM pets WHERE owner_id = ? AND DATE(create_time) = CURDATE()";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, ownerId);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                return rs.getLong(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }

        return 0;
    }

    @Override
    public int updateStatus(Integer petId, String status) {
        String sql = "UPDATE pets SET status = ? WHERE pet_id = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, status);
            pstmt.setInt(2, petId);

            return pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, null);
        }

        return 0;
    }

    @Override
    public int updatePhoto(Integer petId, String photoUrl) {
        String sql = "UPDATE pets SET photo_url = ? WHERE pet_id = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, photoUrl);
            pstmt.setInt(2, petId);

            return pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, null);
        }

        return 0;
    }

    @Override
    public List<Object[]> getPetTypeStatistics() {
        String sql = "SELECT pet_type, COUNT(*) as count FROM pets GROUP BY pet_type ORDER BY count DESC";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Object[]> statistics = new ArrayList<>();

        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                Object[] stat = new Object[2];
                stat[0] = rs.getString("pet_type");
                stat[1] = rs.getLong("count");
                statistics.add(stat);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }

        return statistics;
    }

    @Override
    public List<Object[]> getStatusStatistics() {
        String sql = "SELECT status, COUNT(*) as count FROM pets GROUP BY status ORDER BY count DESC";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Object[]> statistics = new ArrayList<>();

        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                Object[] stat = new Object[2];
                stat[0] = rs.getString("status");
                stat[1] = rs.getLong("count");
                statistics.add(stat);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }

        return statistics;
    }

    /**
     * 根据条件统计宠物数量
     *
     * @param petName 宠物姓名
     * @param petType 宠物类型
     * @param breed 品种
     * @param status 健康状态
     * @param ownerId 主人ID
     * @return long 宠物数量
     */
    private long countByCondition(String petName, String petType, String breed, String status, Integer ownerId) {
        StringBuilder sql = new StringBuilder("SELECT COUNT(*) FROM pets WHERE 1=1");
        List<Object> params = new ArrayList<>();

        if (petName != null && !petName.trim().isEmpty()) {
            sql.append(" AND pet_name LIKE ?");
            params.add("%" + petName.trim() + "%");
        }
        if (petType != null && !petType.trim().isEmpty()) {
            sql.append(" AND pet_type = ?");
            params.add(petType);
        }
        if (breed != null && !breed.trim().isEmpty()) {
            sql.append(" AND breed LIKE ?");
            params.add("%" + breed.trim() + "%");
        }
        if (status != null && !status.trim().isEmpty()) {
            sql.append(" AND status = ?");
            params.add(status);
        }
        if (ownerId != null) {
            sql.append(" AND owner_id = ?");
            params.add(ownerId);
        }

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql.toString());

            for (int i = 0; i < params.size(); i++) {
                pstmt.setObject(i + 1, params.get(i));
            }

            rs = pstmt.executeQuery();
            if (rs.next()) {
                return rs.getLong(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }

        return 0;
    }
}
